package Connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import com.mysql.jdbc.PreparedStatement;

public class database {
	static String idAccount = "";
	static String idQuestion="";
    private String Role="";
	private String userName = "", passWord = "", role = "";
	private Vector content = new Vector<String>();
	private Vector answer = new Vector<String>();
	private Vector id = new Vector<String>();
	private Vector topicId = new Vector<String>();
	private Vector topicName = new Vector<String>();
	private Vector studentName = new Vector<String>();
	private Vector studentResult = new Vector<String>();
	private Vector subject = new Vector<String>();
	private Vector correct = new Vector<String>();
	private Vector contentAnswer = new Vector<String>();
	public Vector showPositionCorrect = new Vector<String>();
	private Vector nameAcc=new Vector<String>();
	private Vector passAcc=new Vector<String>();
	private Vector roleAcc=new Vector<String>();
	private String idAnswer = "";
	public String CorrectPosition = "";
	

	public void setContentAnswer(Vector contentAnswer) {
		contentAnswer = new Vector();
		contentAnswer = contentAnswer;
	}
	public void setNameAcc(Vector NameAcc) {
		nameAcc = new Vector();
		nameAcc = NameAcc;
	}

	public Vector getnameAcc() {
		return nameAcc;
	}
	public void setpassAcc(Vector PassAcc) {
		passAcc = new Vector();
		passAcc = PassAcc;
	}

	public Vector getpassAcc() {
		return passAcc;
	}
	public void setRoleAcc(Vector RoleAcc) {
		roleAcc = new Vector();
		roleAcc = RoleAcc;
	}

	public Vector getRoleAcc() {
		return roleAcc;
	}

	public Vector getContentAnswer() {
		return contentAnswer;
	}

	public void setStudentName(Vector StudentName) {
		studentName = new Vector();
		studentName = StudentName;
	}

	public Vector getStudentName() {
		return studentName;
	}

	public void setStudentResult(Vector StudentResult) {
		studentResult = new Vector();
		studentResult = StudentResult;
	}

	public Vector getStudentResult() {
		return studentResult;
	}

	public void setSubject(Vector Subject) {
		subject = new Vector();
		subject = Subject;
	}

	public Vector getSubject() {
		return subject;
	}

	public void settopicId(Vector TopicId) {
		topicId = new Vector();
		topicId = TopicId;
	}

	public Vector getTopicId() {
		return topicId;
	}

	public void settopicName(Vector TopicName) {
		topicName = new Vector();
		topicName = TopicName;
	}

	public Vector getTopicName() {
		return topicName;
	}

	public void setCorrect(Vector Correct) {
		correct = new Vector();
		correct = Correct;
	}

	public Vector getCorrect() {
		return correct;
	}

	public void setId(Vector ID) {
		id = new Vector();
		id = ID;
	}

	public Vector getId() {
		return id;
	}

	public void setAnswer(Vector Answer) {
		answer = new Vector();
		answer = Answer;
	}

	public Vector getAnswer() {
		return answer;
	}

	public void setContent(Vector Content) {
		content = new Vector();
		content = Content;
	}

	public Vector getContent() {
		return content;
	}

	public void setUserName(String UserName) {
		userName = UserName;
	}

	public String getUserName() {
		return userName;
	}

	public void setPassWord(String PassWord) {
		passWord = PassWord;
	}

	public String getPassWord() {
		return passWord;
	}

	public void setRole(String Role) {
		role = Role;
	}

	public String getRole() {
		return role;
	}

	ArrayList<String> User = new ArrayList<String>();

	public void questionTable(String TopicID) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");

			Statement statement = connection.createStatement();
            
			ResultSet resultSet = statement
					.executeQuery("SELECT idQuestion,Content FROM Question where topicID='"+TopicID+"'");
			content.removeAllElements();
			id.removeAllElements();
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				content.add(resultSet.getString("Content"));
				setContent(content);
				id.add(resultSet.getString("idQuestion"));
				setId(id);
				// User.add(, arg1);
			}
			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}

	}
	public void questionTableAll() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");

			Statement statement = connection.createStatement();
            
			ResultSet resultSet = statement
					.executeQuery("SELECT idQuestion,Content FROM Question");
			content.removeAllElements();
			id.removeAllElements();
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				content.add(resultSet.getString("Content"));
				setContent(content);
				id.add(resultSet.getString("idQuestion"));
				setId(id);
				// User.add(, arg1);
			}
			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}

	}
	public void showConTentAnswer() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();

			ResultSet resultSet = statement
					.executeQuery("SELECT Content FROM Answer");
			while (resultSet.next()) {
				contentAnswer.add(resultSet.getString("content"));
				setContentAnswer(contentAnswer);

			}
			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}
	}

	public void showUser() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();

			ResultSet resultSet = statement
					.executeQuery("SELECT idAccount,accountName,password,Role FROM account ");
			while (resultSet.next()) {
				
		      nameAcc.add(resultSet.getString("accountName"));
			  setNameAcc(nameAcc);
			  passAcc.add(resultSet.getString("password"));
			  setpassAcc(passAcc);
			  roleAcc.add(resultSet.getString("Role"));
			  setRoleAcc(roleAcc);
			
			  
			  
			}
			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}
	}

	
	public void updateAnswer(String value6, String value1, String value2,
			String value3, String value4, String value5) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();

			ResultSet resultSet = statement
					.executeQuery("SELECT idAnswer,idQuestion,content FROM answer where idQuestion='"
							+ value6 + "' group by idQuestion");
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				idAnswer = resultSet.getString("idAnswer");
				// User.add(, arg1)
			}
			String sql1 = "update answer set content = '" + value1
					+ "'where idQuestion='" + value6 + "'and idAnswer='"
					+ idAnswer + "'";
			Statement stm1 = connection.createStatement();
			int rs1 = stm1.executeUpdate(sql1);
			if (rs1 == 1) {

				// lassName=rs.getString("lastname");
				stm1.close();

			}

			idAnswer = Integer.parseInt(idAnswer) + 1 + "";
			String sql2 = "update answer set content = '" + value2
					+ "'where idQuestion='" + value6 + "'and idAnswer='"
					+ idAnswer + "'";
			Statement stm2 = connection.createStatement();
			int rs2 = stm2.executeUpdate(sql2);
			if (rs2 == 1) {

				// lassName=rs.getString("lastname");
				stm2.close();

			}

			idAnswer = Integer.parseInt(idAnswer) + 1 + "";
			String sql3 = "update answer set content = '" + value3
					+ "'where idQuestion='" + value6 + "'and idAnswer='"
					+ idAnswer + "'";
			Statement stm3 = connection.createStatement();
			int rs3 = stm3.executeUpdate(sql3);
			if (rs3 == 1) {

				// lassName=rs.getString("lastname");
				stm3.close();

			}
			idAnswer = Integer.parseInt(idAnswer) + 1 + "";
			String sql4 = "update answer set content = '" + value4
					+ "'where idQuestion='" + value6 + "'and idAnswer='"
					+ idAnswer + "'";
			Statement stm4 = connection.createStatement();
			int rs4 = stm4.executeUpdate(sql4);
			if (rs4 == 1) {

				// lassName=rs.getString("lastname");
				stm4.close();

			}
			String sql5 = "update correctanswer set CorrectPosition = '"
					+ value5 + "'where idQuestion='" + value6 + "'";
			Statement stm5 = connection.createStatement();
			int rs5 = stm5.executeUpdate(sql5);
			if (rs5 == 1) {

				// lassName=rs.getString("lastname");
				stm5.close();

			}

			Statement stm6 = connection.createStatement();
			ResultSet resultSet1 = statement
					.executeQuery("SELECT CorrectPosition FROM correctanswer where idQuestion='"
							+ value6 + "'");

			while (resultSet1.next()) {
				// System.out.println("Question NAME:"
				CorrectPosition = resultSet.getString("CorrectPosition");

				// User.add(, arg1)
			}
			resultSet1.close();

			idAnswer = "";

			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}
	}

	public void showCorrectAnswer(String Value5) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();

			ResultSet resultSet = statement
					.executeQuery("SELECT CorrectPosition FROM correctanswer where idQuestion='"
							+ Value5 + "'");
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				showPositionCorrect.add(resultSet.getString("CorrectPosition"));

				// User.add(, arg1)
			}
			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}
	}

	public void showResult() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();
			String Role = "Student";
			ResultSet resultSet = statement
					.executeQuery("SELECT account.accountName,Result.studentResult,Result.topicName FROM account,Result where Result.idAccount=account.idAccount and account.Role='"
							+ Role + "'");
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				studentName.add(resultSet.getString("account.accountName"));
				setStudentName(studentName);
				studentResult.add(resultSet.getString("Result.studentResult"));
				setStudentResult(studentResult);
				subject.add(resultSet.getString("Result.topicName"));
				setSubject(subject);

				// User.add(, arg1)
			}
			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}

	}
	
	

	public void topicsTable() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();

			ResultSet resultSet = statement
					.executeQuery("SELECT topicID,topicName FROM topics");
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				topicId.add(resultSet.getString("topicID"));
				settopicId(topicId);
				topicName.add(resultSet.getString("topicName"));
				settopicName(topicName);

				// User.add(, arg1);
			}
			resultSet.close();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}

	}

	public void randomContent(String topicID) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement
					.executeQuery("SELECT Question.Content,Question.idQuestion FROM Question,topics where Question.topicID=topics.topicID and topics.topicID = '"
							+ topicID + "'Order By Rand()");
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				content.add(resultSet.getString("Content"));
				setContent(content);
				id.add(resultSet.getInt("idQuestion"));
				setId(id);
				// User.add(, arg1);
			}
			resultSet.first();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}

	}
	public void insertTopic(String value1) {
		try {

			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			
	Vector idQuestion=new Vector<String>();
	Vector Content=new Vector<String>();
	Statement statement = connection.createStatement();
	
			ResultSet resultSet1 = statement
					.executeQuery("SELECT topicID,topicName FROM topics");
			
			
			Vector topicID=new Vector();
			Vector content=new Vector();
			while (resultSet1.next()) {
				// System.out.println("Question NAME:"
	            content.add(resultSet1.getString("topicName")); 
	            topicID.add(resultSet1.getString("topicID"));
			    // User.add(, arg1);
			}
			resultSet1.close();
			
			
					String sql1 = "TRUNCATE topics";
					Statement stm1 = connection.createStatement();
					stm1.executeUpdate(sql1);
			stm1.close();
			Statement stm2 = connection.createStatement();			
			for(int i=0;i<content.size();i++)
				
			{   
				String sql2 = "insert into topics(topicID,topicName) values('"+ topicID.get(i).toString() + "','" + content.get(i).toString() + "')";
				
				stm2.executeUpdate(sql2);
						
			}
			stm2.close();
			String sql = "insert into topics(topicName) values('"+ value1 + "')";
			Statement stm = connection.createStatement();
			stm.executeUpdate(sql);
			

		stm1.close();
			connection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void insertQuestion(String value1, String value2) {
		try {

			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			
	Vector idQuestion=new Vector<String>();
	Vector Content=new Vector<String>();
	Statement statement = connection.createStatement();
	
			ResultSet resultSet1 = statement
					.executeQuery("SELECT topicID,Content FROM Question");
			
			
			Vector topicID=new Vector();
			Vector content=new Vector();
			while (resultSet1.next()) {
				// System.out.println("Question NAME:"
	            content.add(resultSet1.getString("Content")); 
	            topicID.add(resultSet1.getString("topicID"));
			    // User.add(, arg1);
			}
		    	
			
			
					String sql1 = "TRUNCATE question";
					Statement stm1 = connection.createStatement();
					stm1.executeUpdate(sql1);
					
			for(int i=0;i<content.size();i++)
				
			{   
				String sql2 = "insert into question(topicID,Content) values('"+ topicID.get(i).toString() + "','" + content.get(i).toString() + "')";
				Statement stm2 = connection.createStatement();
				stm2.executeUpdate(sql2);
						
			}
										
			String sql = "insert into question(topicID,Content) values('"+ value2 + "','" + value1 + "')";
			Statement stm = connection.createStatement();
			stm.executeUpdate(sql);
			

		
			connection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void insertNewUser(String value1, String value2, String value3) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			
			Statement statement = connection.createStatement();
            String accountName="";
			ResultSet resultSet = statement
					.executeQuery("SELECT idAccount,accountName,Role FROM account");
			
			int idNameAcc=0;
			while (resultSet.next()) {
				// System.out.println("Question NAME:"
				accountName = resultSet.getString("accountName");
				if(accountName.equals(value1))
				{
					idNameAcc=1;
					break;
				}
				
				
				// User.add(, arg1)
			}
			if(idNameAcc==0)
			{
				String sql = "insert into account(accountName,password,Role) values ('" + value1 + "','"
						+ value2 + "','" + value3 + "')";
				Statement stm = connection.createStatement();
				stm.executeUpdate(sql);
				
			}
			Vector idAccount1=new Vector<String>();
			Vector Content=new Vector<String>();
					ResultSet resultSet5 = statement
							.executeQuery("SELECT idAccount,accountName FROM account");
					while (resultSet5.next()) {
						// System.out.println("Question NAME:"
						idAccount1.add(resultSet5.getString("idAccount"));
			            content.add(resultSet5.getString("accountName")); 
					
						// User.add(, arg1);
					}
					
					
					for(int i=1;i<=idAccount1.size();i++)
					{
						if(content.get(i-1).equals(i+""))
						{
						}
						else{
							String sql1 = "update account set idAccount = '" + i
									+ "'where accountName = '" + content.get(i-1).toString() + "'";
							Statement stm1 = connection.createStatement();
							stm1.executeUpdate(sql1);
												
						
						}
					}

			if(value3.equals("Student"))
			{
				Statement stm2 = connection.createStatement();
				
				ResultSet resultSet1 = stm2
					.executeQuery("SELECT idAccount from account where accountName='"+ value1 + "' and Role='"+ value3 + "'");
			while (resultSet1.next()) {
			idAccount=resultSet1.getString("idAccount");
			}
			stm2.close();
			Statement stm3 = connection.createStatement();
			
			ResultSet resultSet2 = stm3
					.executeQuery("SELECT idAccount from Result");
			int idAcc=0;
		    String idAccount2="";
			while (resultSet2.next()) {
			idAccount2=resultSet2.getString("idAccount");
			if(idAccount2.equals(idAccount))
			{
				idAcc=1;
				break;
			}
		
			}
			stm3.close();
			
			if(idAcc==0)
			{   String correctAnswer="0",wrongAnswer="0",studentResult="0",topicName="nothing";  
				String sql1 = "insert into result(idAccount,correctAnswer,wrongAnswer,studentResult,topicName) values ('"
						+ idAccount + "','"
						+ correctAnswer + "','"
						+ wrongAnswer + "','"
						+ studentResult + "','"
						+ topicName + "')";
				Statement stm1 = connection.createStatement();
				stm1.executeUpdate(sql1);
                stm1.close();
				
			}
			
			resultSet.close();
			statement.close();
		
			}
									
				
				
			connection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}


	}

	public void updateResult(String value1, String value2, String value3,
			String value4) {
		try {
			String value = idAccount;
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");

			String sql = "update Result set correctAnswer = '" + value1
					+ "',wrongAnswer = '" + value2 + "',studentResult = '"
					+ value3 + "',topicName = '" + value4
					+ "' where idAccount = '" + value + "'";
			Statement stm = connection.createStatement();
			int rs = stm.executeUpdate(sql);
			if (rs == 1) {

				// lassName=rs.getString("lastname");
				stm.close();
				connection.close();

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updateQuestion(String value1, String value2) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Vector idQuestion=new Vector<String>();
			Vector Content=new Vector<String>();
			Statement statement = connection.createStatement();
			
					ResultSet resultSet1 = statement
							.executeQuery("SELECT topicID,Content FROM Question");
					
					
					Vector topicID=new Vector();
					Vector content=new Vector();
					while (resultSet1.next()) {
						// System.out.println("Question NAME:"
			            content.add(resultSet1.getString("Content")); 
			            topicID.add(resultSet1.getString("topicID"));
					    // User.add(, arg1);
					}
				    	
					
					
							String sql1 = "TRUNCATE question";
							Statement stm1 = connection.createStatement();
							stm1.executeUpdate(sql1);
							
					for(int i=0;i<content.size();i++)
						
					{   
						String sql2 = "insert into question(topicID,Content) values('"+ topicID.get(i).toString() + "','" + content.get(i).toString() + "')";
						Statement stm2 = connection.createStatement();
						stm2.executeUpdate(sql2);
								
					}
			
			String sql = "update Question set Content = '" + value2
					+ "' where idQuestion = '" + value1 + "'";
			Statement stm = connection.createStatement();
			int rs = stm.executeUpdate(sql);
			if (rs == 1) {

				// lassName=rs.getString("lastname");
				stm.close();
				connection.close();

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	public void updateTopic(String value1, String value2) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Vector idQuestion=new Vector<String>();
			Vector Content=new Vector<String>();
			Statement statement = connection.createStatement();
			
					ResultSet resultSet1 = statement
							.executeQuery("SELECT topicID,topicName FROM topics");
					
					
					Vector topicID=new Vector();
					Vector content=new Vector();
					while (resultSet1.next()) {
						// System.out.println("Question NAME:"
			            content.add(resultSet1.getString("topicName")); 
			            topicID.add(resultSet1.getString("topicID"));
					    // User.add(, arg1);
					}
				    	
					
					
							String sql1 = "TRUNCATE topics";
							Statement stm1 = connection.createStatement();
							stm1.executeUpdate(sql1);
							
					for(int i=0;i<content.size();i++)
						
					{   
						String sql2 = "insert into topics(topicID,topicName) values('"+ topicID.get(i).toString() + "','" + content.get(i).toString() + "')";
						Statement stm2 = connection.createStatement();
						stm2.executeUpdate(sql2);
								
					}
			
			String sql = "update topics set topicName = '" + value2
					+ "' where topicID = '" + value1 + "'";
			Statement stm = connection.createStatement();
			int rs = stm.executeUpdate(sql);
			if (rs == 1) {

				// lassName=rs.getString("lastname");
				stm.close();
				connection.close();

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	
	public void updateUser(String value1, String value2,String value3,String value4) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");

			String sql = "update account set accountName = '" + value2
					+"',password = '" + value3+  "',Role = '" + value4+  "'where idAccount = '" + value1 + "'";
			Statement stm = connection.createStatement();
			int rs = stm.executeUpdate(sql);
			if (rs == 1) {

				// lassName=rs.getString("lastname");
				stm.close();
				connection.close();

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	public void deleteuUsers(String value1) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			String sql = "delete from account where idAccount = '" + value1
					+ "'";
			Statement stm = connection.createStatement();
			int rs = stm.executeUpdate(sql);
            String sql1 = "delete from result where idAccount = '"
					+ value1 + "'";
			Statement stm1 = connection.createStatement();
			int rs1 = stm1.executeUpdate(sql1);
			stm1.close();
		 
			connection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	
	
	
	
	
	
	
	public void deleteQuestion(String value1) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Vector idQuestion=new Vector<String>();
			Vector Content=new Vector<String>();
			Statement statement = connection.createStatement();
			
					ResultSet resultSet1 = statement
							.executeQuery("SELECT topicID,Content FROM Question");
					
					
					Vector topicID=new Vector();
					Vector content=new Vector();
					while (resultSet1.next()) {
						// System.out.println("Question NAME:"
			            content.add(resultSet1.getString("Content")); 
			            topicID.add(resultSet1.getString("topicID"));
					    // User.add(, arg1);
					}
				    	
					
					
							String sql1 = "TRUNCATE question";
							Statement stm1 = connection.createStatement();
							stm1.executeUpdate(sql1);
							
					for(int i=0;i<content.size();i++)
						
					{   
						String sql2 = "insert into question(topicID,Content) values('"+ topicID.get(i).toString() + "','" + content.get(i).toString() + "')";
						Statement stm2 = connection.createStatement();
						stm2.executeUpdate(sql2);
								
					}
			
			String sql = "delete from answer where idQuestion = '" + value1
					+ "'";
			Statement stm = connection.createStatement();
			int rs = stm.executeUpdate(sql);
            String sql3 = "delete from correctanswer where idQuestion = '"
					+ value1 + "'";
            
			Statement stm3 = connection.createStatement();
			int rs1 = stm3.executeUpdate(sql3);
			stm1.close();
			String sql2 = "delete from question where idQuestion = '" + value1
					+ "'";
			Statement stm2 = connection.createStatement();
			int rs2 = stm2.executeUpdate(sql2);
		
			stm2.close();
	     
			connection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	public void deleteTopic(String value1) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Vector idQuestion=new Vector<String>();
			Vector Content=new Vector<String>();
			Statement statement = connection.createStatement();
			
					ResultSet resultSet1 = statement
							.executeQuery("SELECT topicID,topicName FROM topics");
					
					
					Vector topicID=new Vector();
					Vector content=new Vector();
					while (resultSet1.next()) {
						// System.out.println("Question NAME:"
			            content.add(resultSet1.getString("topicName")); 
			            topicID.add(resultSet1.getString("topicID"));
					    // User.add(, arg1);
					}
				    	
					
					
							String sql1 = "TRUNCATE topics";
							Statement stm1 = connection.createStatement();
							stm1.executeUpdate(sql1);
							
					for(int i=0;i<content.size();i++)
						
					{   
						String sql2 = "insert into topics(topicID,topicName) values('"+ topicID.get(i).toString() + "','" + content.get(i).toString() + "')";
						Statement stm2 = connection.createStatement();
						stm2.executeUpdate(sql2);
								
					}
			
				String sql2 = "delete from topics where topicID = '" + value1
					+ "'";
			Statement stm2 = connection.createStatement();
			int rs2 = stm2.executeUpdate(sql2);
		
			stm2.close();
			String sql6 = "delete from question where topicID = '" + value1
					+ "'";
			Statement stm6 = connection.createStatement();
			int rs6 = stm6.executeUpdate(sql6);
		
			stm6.close();
		
			String sql3 = "delete from answer where topicID = '" + value1
					+ "'";
			Statement stm3 = connection.createStatement();
			int rs3 = stm3.executeUpdate(sql3);
		
			stm3.close();
	     
			String sql4 = "delete from correctanswer where topicID = '" + value1
					+ "'";
			Statement stm4 = connection.createStatement();
			int rs4 = stm4.executeUpdate(sql4);
		
			stm4.close();
	     
			connection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void insertAnswer(String value1, String value2,
			String value3, String value4, String value5, String value6) {
		try {

			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();
			
		ResultSet	resultSet = statement.executeQuery("SELECT idQuestion FROM Question");
		resultSet.last();
		String value7=resultSet.getString("idQuestion");	
	
			
			
			
			
			String sql = "insert into answer(idQuestion,Content,topicID) values ('"
					+ value7 + "','" + value1 + "','" + value6 + "')";
			Statement stm = connection.createStatement();
			stm.executeUpdate(sql);
			String sql1 = "insert into answer(idQuestion,Content,topicID) values ('"
					+ value7 + "','" + value2 + "','" + value6 + "')";
			Statement stm1 = connection.createStatement();
			stm1.executeUpdate(sql1);
			String sql2 = "insert into answer(idQuestion,Content,topicID) values ('"
					+ value7 + "','" + value3 + "','" + value6 + "')";
			Statement stm2 = connection.createStatement();
			stm2.executeUpdate(sql2);
			String sql3 = "insert into answer(idQuestion,Content,topicID) values ('"
					+ value7 + "','" + value4 + "','" + value6 + "')";
			Statement stm3 = connection.createStatement();
			stm3.executeUpdate(sql3);
			String sql4 = "insert into correctanswer(idQuestion,CorrectPosition,topicID) values ('"
					+ value7
					+ "','"
					+ value5
					+ "','"
				
					+ value6
					+ "')";
			Statement stm4 = connection.createStatement();
			stm4.executeUpdate(sql4);
			
			connection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void checkAnswer(String topicID) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();
			ResultSet resultSet = null;
			correct.removeAllElements();
			for (int i = 0; i < id.size(); i++) {
				resultSet = statement
						.executeQuery("SELECT CorrectPosition FROM Question,CorrectAnswer where CorrectAnswer.topicID='"
								+ topicID
								+ "' and  Question.idQuestion=CorrectAnswer.idQuestion and CorrectAnswer.idQuestion="
								+ getId().get(i).toString());
				while (resultSet.next()) {
					// System.out.println("Question NAME:"
					correct.add(resultSet
							.getString("CorrectAnswer.CorrectPosition"));
					setCorrect(correct);
					// User.add(, arg1);
				}
			}
			resultSet.first();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}

	}

	public void randomAnswer(String topicID) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/examination", "root",
					"lehaiha");
			Statement statement = connection.createStatement();
			ResultSet resultSet = null;
			for (int i = 0; i < id.size(); i++) {
				resultSet = statement
						.executeQuery("select answer.idQuestion,answer.Content from question,answer where  answer.idQuestion=question.idQuestion and question.topicID='"
								+ topicID
								+ "' and question.idQuestion= '"+ getId().get(i).toString()+"'");
				while (resultSet.next()) {
					// System.out.println("Question NAME:"
					answer.add(resultSet.getString("answer.Content"));
					setAnswer(answer);
					// User.add(, arg1);
				}
			}
			resultSet.first();
			connection.close();

		} catch (Exception e) {
			// e.printStackTrace();
		}

	}

	public String checkLogin(String userName, String passWord, String Role) {
		String status = "";
		try {
			{
				// System.out.println(userName);
				// System.out.println(passWord);
				String name = "", pass = "", role = "";
				Class.forName("com.mysql.jdbc.Driver");
				Connection connection = DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/examination", "root",
						"lehaiha");
				Statement statement = connection.createStatement();
				ResultSet resultSet = statement
						.executeQuery("SELECT accountName,password,Role,idAccount FROM Account Where accountName='"
								+ userName
								+ "'and password='"
								+ passWord
								+ "'and Role='" + Role + "'");
				while (resultSet.next()) {
					// System.out.println("Question NAME:"
					name = resultSet.getString("accountName");
					pass = resultSet.getString("password");
					role = resultSet.getString("Role");
					idAccount = resultSet.getString("idAccount");
					//
					// System.out.println(resultSet.getString("accountName"));
					// System.out.println(resultSet.getString("password"));

				}
				connection.close();

				setUserName(name);
				setPassWord(pass);
				setRole(role);

				// resultSet.first();
				// System.out.println(getUserName());
				// System.out.println(getPassWord());

				if (userName.trim().equals(getUserName())
						&& passWord.trim().equals(getPassWord())
						&& role.trim().equals("Admin")) {
					status = "Admin";
				} else if (userName.trim().equals(getUserName())
						&& passWord.trim().equals(getPassWord())
						&& role.trim().equals("Student"))

				{
					status = "Student";
				} else {
					status = "";
				}
			}
		} catch (Exception e) {
			// e.printStackTrace();
		}

		return status;

	}

}
